﻿/* CSharp project - 2nd chance
 * Autor: Neuza Figueira*/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data.Common;
using System.Data.SQLite;//Reference: http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki
using LumenWorks.Framework.IO.Csv; //A reader to access to CSV data. Reference: http://www.codeproject.com/KB/database/CsvReader.aspx
using System.Text.RegularExpressions;
using System.Windows.Forms;
using System.Data;

namespace Project
{
    class Database
    {
        List<string> listOfYears;
         
        //To create the database with the information from the given csv files
        public void createDatabase()
        {
            File.Delete("Database.db");
            //to connect to the database
            SQLiteConnection connection = new SQLiteConnection("Data Source=Database.db;FailIfMissing=false;");
            connection.Open();

            //database transaction
            SQLiteTransaction transaction = connection.BeginTransaction();

            //command
            SQLiteCommand cmd = connection.CreateCommand();

            //to create the table
            cmd.CommandText = "CREATE TABLE IF NOT EXISTS Database(codigo_docente integer," + "nome_completo_docente text," +
                                      "ultimo_grau text," + "curso text," +  "categoria text," + "regime_tempo text," + "ano_lectivo text," +
                                      "tipo_estabelecimento text," + "nome_estabelecimento text)";
            cmd.ExecuteNonQuery();

            //to make sure all the csv files are used
            for (int i = 0; i <= 10; i++)
            {
                //CSV Reader
                CsvReader reader = new CsvReader(new StreamReader("rebcsv/reb" + i + ".csv"), true);
                // to insert the information into the database
                while (reader.ReadNextRecord())
                {
                    cmd.CommandText = string.Format("INSERT INTO Database VALUES('{0}','{1}','{2}','{3}','{4}'," +
                                                        "'{5}','{6}','{7}','{8}')", reader[0].Replace("\'", ""),
                                                        reader[1].Replace("\'", ""), reader[2].Replace("\'", ""),
                                                        reader[3].Replace("\'", ""), reader[4].Replace("\'", ""),
                                                        reader[5].Replace("\'", ""), reader[6].Replace("\'", ""),
                                                        reader[7].Replace("\'", ""), reader[8].Replace("\'", ""));
                    cmd.ExecuteNonQuery();
                }
            }

            transaction.Commit();
            connection.Close();

            DialogResult result = MessageBox.Show("Database completed!", "Info", MessageBoxButtons.OK);
        }

        public void openDatabase()
        {
            SQLiteConnection connection = new SQLiteConnection("Data Source=Database.db;FailIfMissing=false;");
            connection.Open();
        }


        public List<string> getYears()
        {
            listOfYears = new List<string>();
            SQLiteConnection connection = new SQLiteConnection();
            connection.ConnectionString = "Data Source=Database.db;FailIfMissing=False;";
            connection.Open();

            SQLiteCommand cmd = connection.CreateCommand();
            cmd.Connection = connection;

            cmd.CommandText = "SELECT distinct ano_lectivo FROM Database";

            SQLiteDataReader reader;

            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                listOfYears.Add((string)reader[0]);
            }
            listOfYears.Sort();

            return listOfYears;
        }


        //Number of establishments per year
        public List<Statistic1> Statistic1(string selectedYear)
        {
            //To create a list with objects from statistic 1
            List<Statistic1> Stat1 = new List<Statistic1>();

            SQLiteConnection connection = new SQLiteConnection("Data Source = Database.db;FailIfMissing=False;");
            connection.Open();

            SQLiteTransaction transaction = connection.BeginTransaction();

            SQLiteCommand cmd = connection.CreateCommand();

            //all the information from database is selected
            cmd.CommandText = "SELECT * FROM Database";
            cmd.CommandType = CommandType.Text;

            //to save the result from the query
            DbDataReader reader = cmd.ExecuteReader();
       
            int number = 0;
            while (reader.Read())
            {
                if ((string)reader[6] == selectedYear)
                {
                    string establishment = (string)reader[8];
                    number++;
                }
            }

                Stat1.Add(new Statistic1
                {
                    Year = selectedYear,
                    Establishments = number
                });
                              
            transaction.Commit();
            connection.Close();

            //return list of objects
            return Stat1;
        }

        public List<Statistic2> Statistic2()
        {
            //To create a list with objects from statistic 2
            List<Statistic2> Stat2 = new List<Statistic2>();
         
            SQLiteConnection connection = new SQLiteConnection("Data Source = Database.db;FailIfMissing=False;");
            connection.Open();

            SQLiteTransaction transaction = connection.BeginTransaction();
            SQLiteCommand cmd = connection.CreateCommand();

            //all the information from database is selected
            cmd.CommandText = "SELECT * FROM Database";
            cmd.CommandType = CommandType.Text;

            //to save the result from the query
            DbDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                Stat2.Add(new Statistic2
                {
                    Year = (string)reader[6],
                    Categorie = (string)reader[4]
                });
            }

            //using LINQ for the statistics 
            var line = from data in Stat2
                        group data by new { data.Year, data.Categorie } into orderlist
                        select new
                        {
                            orderlist.Key.Year,
                            orderlist.Key.Categorie,
                            counter = orderlist.Count()
                        };

            List<Statistic2> Stat2Obj = new List<Statistic2>();
            
            //the result from LINQ query is returned to an object list 
            foreach (var data in line)
            {
                Stat2Obj.Add(new Statistic2
                {
                    Year = data.Year,
                    Categorie = data.Categorie,
                    Teachers = data.counter
                });
            }

            transaction.Commit();
            connection.Close();

            //return the list to allow printing on the interface
            return Stat2Obj;
        }

        public List<Statistic3> Statistic3()
        {
            //To create a list with objects from statistic 3
            List<Statistic3> Stat3 = new List<Statistic3>();

            SQLiteConnection connection = new SQLiteConnection("Data Source=Database.db;FailIfMissing=False;");
            connection.Open();

            SQLiteTransaction transaction = connection.BeginTransaction();

            SQLiteCommand cmd = connection.CreateCommand();

            //all the information from database is selected
            cmd.CommandText = "SELECT * FROM Database";
            cmd.CommandType = CommandType.Text;

            //to save the result from the query
            DbDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                Stat3.Add(new Statistic3
                {
                    Establishment = (string)reader[8],
                    Categorie = (string)reader[4]
                });
            }

            //using LINQ for the statistics
            var line = from data in Stat3
                        group data by new { data.Establishment, data.Categorie } into orderlist
                        select new
                        {
                            orderlist.Key.Establishment,
                            orderlist.Key.Categorie,
                            counter = orderlist.Count()
                        };

            List<Statistic3> Stat3Obj = new List<Statistic3>();

            //the result from LINQ query is returned to an object list
            foreach (var data in line)
            {
                Stat3Obj.Add(new Statistic3
                {
                    Establishment = data.Establishment,
                    Categorie = data.Categorie,
                    Teachers = data.counter
                });
            }

            transaction.Commit();
            connection.Close();

            //return the list to allow printing on the interface
            return Stat3Obj;
        }

    }
}
